package dbtools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import dbtools.queries.SelectStatement;
import dbtools.structures.DBCond;
import dbtools.structures.DBField;
import dbtools.structures.DBKeyElement;
import dbtools.structures.DBTable;
import dbtools.structures.DBUniqueKey;

public class DBTransaction {

	private Connection connection=null;
	private DBDatabase database;
	private String catalog=null;
	private String schema=null;

	public DBTransaction(Connection connection, DBDatabase database) throws SQLException {
		this.connection = connection;
		this.database = database;
		connection.setAutoCommit(false);
	}
	
	public DBDatabase getDatabase(){
		return database;
	}

	public void insert(DBTable table) {
		try{
			String sql = "INSERT INTO "+table.getTableName()+" VALUES(";
			String flds = "";
			ArrayList<Object> values = new ArrayList<Object>();
			for (DBField dbFld : table.getAllFields()){
				if (flds.length() > 0){
					flds += ",";
				}
				flds += "?";
				dbFld.beforeInsert(this);
				values.add(dbFld.getValueForSQL());
			}
			sql +=flds+")";
			execUpdate(sql,values.toArray());
		}catch(Throwable t){
			t.printStackTrace();
		}
		
	}

	public void delete(DBTable table) throws SQLException {
		if (table.getAllKeys().length == 0){
			throw new Error("Cannot update table with no primary key");
		}
		DBUniqueKey primaryKey = table.getAllKeys()[0];
		ArrayList<Object> values = new ArrayList<Object>();
		
		String conds = "";

		for (DBKeyElement elem : primaryKey.getDbKeyElements()){
			if (!conds.equals("")){
				conds += ",";
			}
			conds += " "+elem.getTheField().getName()+"=?";
			values.add(elem.getTheField().getValueForSQL());
		}
		String sql = "DELETE FROM "+table.getTableName()+"  WHERE "+conds;
		execUpdate(sql, values.toArray());
	}

	public void update(DBTable table) throws SQLException {
		if (table.getAllKeys().length == 0){
			throw new Error("Cannot update table with no primary key");
		}
		ArrayList<Object> values = new ArrayList<Object>();
		DBUniqueKey primaryKey = table.getAllKeys()[0];
		String assignments = "";
		for (DBField fld : table.getAllFields()){
			if (primaryKey.containsField(fld)){
				continue;
			}
			if (!assignments.equals("")){
				assignments += ",";
			}
			assignments += " "+fld.getName()+"=? ";
			fld.beforeUpdate(this);
			values.add(fld.getValueForSQL());
		}
		String conds = "";

		for (DBKeyElement elem : primaryKey.getDbKeyElements()){
			if (!conds.equals("")){
				conds += ",";
			}
			conds += " "+elem.getTheField().getName()+"=?";
			values.add(elem.getTheField().getValueForSQL());
		}
		String sql = "UPDATE "+table.getTableName()+" SET "+assignments+" WHERE "+conds;
		execUpdate(sql, values.toArray());
	}
	
	public <T extends DBTable> T getByMainKey(Class<T> type, 
			Object... keyValues) throws SQLException {
		DBTable table;
		table = DBFactory.createInstance(type);
		DBUniqueKey key = table.getAllKeys()[0];
		String sql = "select * from " + table.getTableName()
				+ " where ";
		ArrayList<Object> values = new ArrayList<Object>();
		String conds = "";
		int i = 0;
		for (DBKeyElement elem : key.getDbKeyElements()) {
			if (conds.length() > 0) {
				conds += " and ";
			}
			conds += (elem.getTheField().getName()) + "=?";
			elem.getTheField().setValue(keyValues[i]);
			values.add(elem.getTheField().getValueForSQL());
			i++;
		}
		sql += conds;

		ResultSet rs = execQuery1(sql, values.toArray());
		if (rs.next()) {
			DBResultSet.get(rs, table);
			rs.close();
			return (T) table;
		}
		rs.close();
		return null;
	}

	public void commit() throws SQLException {
		connection.commit();
		connection.close();
	}
	
	public void rollback() throws SQLException{
		connection.rollback();
		connection.close();
	}

	public DBResultSet executeQuery(Object...parameters) {
		return null;
	}

	public void execUpdate(String sql, Object...args) throws SQLException{
		PreparedStatement statement = connection.prepareStatement(sql);
		for (int i=0; i<args.length; i++){
			statement.setObject(i+1, args[i]);
		}
		statement.execute();
		statement.close();
	}
	
	public ResultSet execQuery1(String sql, Object...args) throws SQLException{
		PreparedStatement statement = connection.prepareStatement(sql);
		for (int i=0; i<args.length; i++){
			statement.setObject(i+1, args[i]);
		}
		return statement.executeQuery();
	}
	
	
	public DBResultSet execFreeQuery(DBField[] selectedFields, String sql, Object...args) throws SQLException{
		PreparedStatement statement = connection.prepareStatement(sql);
		for (int i=0; i<args.length; i++){
			statement.setObject(i+1, args[i]);
		}
		ResultSet rs = statement.executeQuery();
		return new DBResultSet(rs,selectedFields);
	}

	
	public void createTable(Class<? extends DBTable> tableClass) {
		try{
			DBTable table = DBFactory.createInstance(tableClass);
			ResultSet md = connection.getMetaData().getTables(catalog, schema, table.getTableName(), null);
			if (md.next()){
				md.close();
				return;
			}
			md.close();
			dropTable(table);
			String sql = "CREATE TABLE "+table.getTableName()+"(";
			String flds = "";
			for (DBField f : table.getAllFields()){
				if (flds.length() > 0){
					flds += ",";
				}
				flds += f.getName()+" "+database.getSqlType(f);
			}
			sql +=flds+")";
			execUpdate(sql);
			for (DBUniqueKey index : table.getAllKeys()){
				createIndex(index,table);
			}
		}catch(Throwable t){
			t.printStackTrace();
		}
	}

	public void dropTable(DBTable table) {
		try{
			execUpdate("drop table "+table.getTableName());
		}catch(Throwable t){
			
		}
	}

	private void createIndex(DBUniqueKey index, DBTable table) throws SQLException {
		String sql = "CREATE UNIQUE INDEX "+index.getName()+" ON "+table.getTableName()+" (";
		String elements = "";
		for (DBKeyElement elem : index.getDbKeyElements()){
			if (elements.length() > 0){
				elements += ",";
			}
			elements += elem.getTheField().getName() + (elem.isAscending() ? " ASC": " DESC");
		}
		sql += elements+")";
		execUpdate(sql);
	}

	public SelectStatement select(DBField...fields) {
		return new SelectStatement(this,fields);
	}

	public DBResultSet execSelect(DBTable[] tables, DBField[] fields,
			DBCond[] conditions, DBField[] orderByFields) {
		return null;
	}

	
}
